const Service = require('egg').Service;
const _ = require('underscore');

class TaskService extends Service {

  async create(task) {
    const lastTaskId = await this.app.mysql.query(`SELECT MAX(id) FROM task;`);
    const taskCreate = {
      ...task,
      id: lastTaskId[0]['MAX(id)'] + 1,
      createAt: new Date(),
    };
    const result = await this.app.mysql.insert('task', taskCreate);

    return result;
  }

  async update(task) {
    const result = await this.app.mysql.update('task', task);

    return result;
  }

  async comment(comment) {
    const lastCommentId = await this.app.mysql.query(`SELECT MAX(id) FROM comment;`);
    const commentCreate = {
      ...comment,
      id: lastCommentId[0]['MAX(id)'] + 1,
      createAt: new Date(),
    };
    const result = await this.app.mysql.insert('comment', commentCreate);
    const row = {
      id: comment.taskId,
      _isCommented: 1,
    };
    const isCommented = await this.app.mysql.update('task', row);
    return result;
  }

  async findTree(member) {

    let taskDateTree = await this.app.mysql.query(`
    SELECT
      concat( YEAR ( T.date ), "年第", WEEK ( T.date ), "周" ) AS \`groupTitle\`,
      DATE_FORMAT(T.date,"%m-%d") AS \`date\`,
      DATE_FORMAT(T.date,"%Y-%m-%d") AS \`queryDate\`,
      Date_format( T.date, "%W" ) AS \`dayOfWeek\`,
      COUNT( T.id ) AS countTask,
      COUNT( T._isCommented ) AS countComment
    FROM
      task AS T
      INNER JOIN ( SELECT * FROM member WHERE _sortBy LIKE ? OR _sortBy = ? ) AS M ON M.id = T.memberId
    GROUP BY
      T.date
    ORDER BY
      T.date DESC;
    `, [member._sortBy + '-%', member._sortBy]);

    let days = {
      Monday: "星期一",
      Tuesday: "星期二",
      Wednesday: "星期三",
      Thursday: "星期四",
      Friday: "星期五",
      Saturday: "星期六",
      Sunday: "星期日"
    };

    taskDateTree = taskDateTree.map(item => {
      item.dayOfWeek = days[item.dayOfWeek];
      return item;
    });

    taskDateTree = _.chain(taskDateTree)
      .groupBy("groupTitle")
      .values()
      .value();

    return taskDateTree;
  }

  async findMemberTreeCount(member) {

    let memberTree = await this.app.mysql.query(`
    SELECT
      A.*,
      B._count,
      C.isAdmin,
      C.auth,
      C.id AS userId,
      T._countTask,
      T._countCommented
    FROM
      ( SELECT * FROM member WHERE _sortBy LIKE ? OR _sortBy = ? ) AS A
      LEFT JOIN org AS B ON A.orgId = B.id
      LEFT JOIN user AS C ON A.id = C.memberId
      LEFT JOIN (SELECT
        task.memberId,
        COUNT(task.id) AS _countTask,
        COUNT(task._isCommented) AS _countCommented
        FROM task
        GROUP BY
          task.memberId
        ) AS T ON A.id = T.memberId
    ORDER BY
      A._sortBy,
      A.joinState DESC,
      A.sortId;
    `, [member._sortBy + '-%', member._sortBy]);

    memberTree = memberTree.map((item) => {
      item._levelNo -= member._levelNo;
      return item;
    });


    return memberTree;
  }

  async serveMemberTree(member, options) {
    let serveMemberTree = await this.app.mysql.query(`
    SELECT
      A.*,
      B._count,
      C.isAdmin,
      C.auth,
      C.id AS userId
    FROM
      ( SELECT * FROM member WHERE _sortBy LIKE ? && joinState = 1 ) AS A
      LEFT JOIN org AS B ON A.orgId = B.id
      LEFT JOIN user AS C ON A.id = C.memberId
    ORDER BY
      A._sortBy,
      A.sortId;
    `, [member._sortBy + '%']);

    if (!options) return serveMemberTree;

    if (options.mode === 'select') {
      const level0 = serveMemberTree[0]._levelNo;

      serveMemberTree = serveMemberTree.map((item) => {
        item.title = '-'.repeat(item._levelNo - level0 + 1) + '【' + item._orgName + '】' + item.netName;
        item.value = item.id;
        return item;
      });
    } else if (options.mode === 'list') {
      const level0 = serveMemberTree[0]._levelNo;

      serveMemberTree = serveMemberTree.map((item) => {
        item._levelNo = item._levelNo - level0;
        return item;
      });
    }
    return serveMemberTree;

  }
}

module.exports = TaskService;